SeoprofesionalDb = function() {
var database = openDatabase("seoprofesional", "", "SeoProfesional Toolbar Data", 1000000);
if (null==database) {
  // open other version and do changeversion ?!?
  console.log("database==null");
  throw Exception("Could not open the database");
}
console.log("SeoProfesional DATABASE:", database.version);

var seopdb = function() {
  this.database = database;
  this.initialized = false;
}
seopdb.prototype.isInitialized = function() {  return this.initialized; };

logSuccess = function(tx, res) { console.log("DB SUCCESS"); }
logError = function(tx, error) { console.log("DB ERROR", error); }

database.transaction(
  function(tx) {
    tx.executeSql("CREATE TABLE IF NOT EXISTS whitelist (url TEXT PRIMARY KEY)", [],
      logSuccess, logError);
    tx.executeSql("CREATE TABLE IF NOT EXISTS urls (uid INTEGER PRIMARY KEY, url TEXT)", [],
      logSuccess, logError);
    tx.executeSql("CREATE TABLE IF NOT EXISTS ranks (uid INTEGER NOT NULL, rank INTEGER, tm INTEGER, value REAL)", [],
      logSuccess, logError);

    seopdb.initialized = true;
  },
  function() {
    seopdb.initialized = false;
    console.log("database creation - transaction error");
  },
  function() {
    seopdb.initialized = true;
    console.log("database creation - transaction OK");
  }
);

if (""==database.version) {
  database.changeVersion("", "1.1",
    function(tx) {},
    function(error) { console.log("Error changing version '' to '1.1'") },
    function() { console.log("Version change '' to '1.1' successful") }
  );
}
if ("1.0"==database.version) {
  // Seznam have changed their rank
  database.changeVersion("1.0", "1.1",
    function(tx) {
      tx.executeSql("UPDATE ranks SET value=ROUND(value/10) WHERE rank=2", [], logSuccess, logError);
    },
    function(error) { console.log("Error changing version '1.0' to '1.1'") },
    function() { console.log("Version change '1.0' to '1.1' successful") }
  );
}

seopdb.prototype.is_whitelisted = function(uri, cb) {
  this.database.transaction(
    function(tx) {
      tx.executeSql("SELECT url FROM whitelist WHERE url=substr(?1, 1, length(url))", [ uri ],
        function(tx, resultset) {
          var longest = "";
          for (var i = 0; i<resultset.rows.length; i++) {
            var row = resultset.rows.item(i);
            if (row["url"].length>longest.length) {
              longest = row["url"];
            }
          }
          cb(""==longest ? null : longest);
        },
        function(tx, error) {
          cb(null);
        }
      );
    }
  );
};
seopdb.prototype.get_whitelist = function(cb) {
  this.database.transaction(
    function(tx) {
      tx.executeSql(
        "SELECT url FROM whitelist ORDER BY url", [], 
        function(tx, resultset) {
          for (var i = 0; i<resultset.rows.length;i++) {
              var row = resultset.rows.item(i);
              cb(row["url"]);
          }
        }
      );
    }
  );
};
seopdb.prototype.whitelist = function(uri) {
  this.database.transaction(
    function(tx) {
      tx.executeSql("INSERT INTO whitelist (url) VALUES (?1)", [ uri ]);
    }
  );
};
seopdb.prototype.un_whitelist = function(uri, cb) {
    this.database.transaction(
        function(tx) {
            tx.executeSql("DELETE FROM whitelist WHERE url=?1", [uri]);
        }
    );
};
seopdb.prototype.get_rank = function(uri, rank, cb) {
  this.database.transaction(
    function(tx) {
      tx.executeSql(
        "SELECT ranks.value, ranks.tm FROM ranks, urls " + 
        "WHERE ranks.uid=urls.uid AND ranks.rank=?1 AND urls.url=?2 " + 
        "ORDER BY tm DESC LIMIT 1", [rank, uri], 
        function(tx, resultset) {
          if (resultset.rows.length) {
            var row = resultset.rows.item(0);
            var tm = new Date();
            tm.setTime(row["tm"]);

            cb(tm, row["value"]);
          } else {
                        cb(null, null);
                    }
        },
        function(tx, error) {
          cb(null, null);
        }
      );
    }
  );
};
seopdb.prototype.get_or_plant_uid = function(uri, cb) {
  this.database.transaction(
    function(tx) {
      tx.executeSql("SELECT uid FROM urls WHERE url=?1", [ uri ],
        function(tx, resultset) {
          if (resultset.rows.length) {
            var row = resultset.rows.item(0);
            cb(row["uid"]);
            return ;
          }
          tx.executeSql("INSERT INTO urls (url) VALUES (?1)", [ uri ],
            function(tx, resultset) {
              if (resultset.insertId>0) {
                cb(resultset.insertId);
              }
            }
          )
        }
      );
    }
  );
};
seopdb.prototype.add_rank = function(uri, rank, val) {
  var that = this;
  this.is_whitelisted(uri, function(wl) {
    if (null!=wl) {
      that.get_or_plant_uid(uri, function(uid) {
        // get the last value for this uid and rank
        that.database.transaction(
          function(tx) {
            tx.executeSql("SELECT rowid, value FROM ranks WHERE uid=?1 AND rank=?2 ORDER BY tm DESC LIMIT 1", [uid, rank],
              function(tx, resultset) {
                var last_val = null;
                var row_id = null;
                if (resultset.rows.length) {
                  last_val = resultset.rows.item(0)["value"];
                  row_id = resultset.rows.item(0)["rowid"];
                }
                var dt = (new Date()).getTime();
                if (val!=last_val) {
                  tx.executeSql("INSERT INTO ranks VALUES (?1, ?2, ?3, ?4)", [uid, rank, dt, val]);
                  tx.executeSql("INSERT INTO ranks VALUES (?1, ?2, ?3, ?4)", [uid, rank, dt+1, val]);
                } else {
                  tx.executeSql("UPDATE ranks SET tm=?1 WHERE rowid=?2", [dt, row_id]);
                }
              }
            );
          }
        );
      });
    }
  });
};
seopdb.prototype.get_tl = function(uri, rank, cb) {
  this.database.transaction(function(tx) {
    tx.executeSql(
      "SELECT ranks.value, ranks.tm FROM ranks, urls " + 
      "WHERE ranks.uid=urls.uid AND ranks.rank=?1 AND urls.url=?2 " + 
      "ORDER BY tm", [ rank, uri ],
      function(tx, resultset) {
        var ret = [];
        for (var i = 0; i<resultset.rows.length; i++) {
          var row = resultset.rows.item(i);
          ret.push([ row["tm"], row["value"] ]);
        }
        if (ret.length) {
          cb(ret);
        }
      },
      function(tx, error) {
        console.log("DB: GET_TL ERROR", arguments);
      }
    );
  });
};
seopdb.prototype.get_sub = function(uri, cb) {
  this.database.transaction(function(tx) {
    tx.executeSql(
            "SELECT url FROM urls WHERE ?=substr(url, 1, ?) ORDER BY url", [ uri, uri.length ],
      function(tx, resultset) {
        var subs = [];
        for (var i = 0; i<resultset.rows.length; i++) {
          var row = resultset.rows.item(i);
          subs.push(resultset.rows.item(i)["url"]);
        }
        cb(subs);
      }
    );
  });
};
seopdb.prototype.forget_record = function(uri, cb) {
  this.database.transaction(function(tx) {
    tx.executeSql("SELECT uid FROM urls WHERE ?=url", [uri],
      function(tx, resultset) {
        for (var i = 0;i<resultset.rows.length; i++) {
          tx.executeSql("DELETE FROM urls WHERE ?=uid", [resultset.rows.item(i)["uid"]]);
          tx.executeSql("DELETE FROM ranks WHERE ?=uid", [resultset.rows.item(i)["uid"]]);
        }
      }
    );
  },
  cb, cb);
};
seopdb.prototype.killorphans = function(cb_ok, cb_error) {
  var that = this;
  console.log("THIS, THAT", this, that);
  this.database.transaction(function(tx) {
    tx.executeSql("SELECT uid, url FROM urls", [],
        function(tx, resultset) {
          for (var i = 0; i<resultset.rows.length; i++) {
            var row = resultset.rows.item(i);
            var uid = row["uid"];
            var url = row["url"];
            that._killorphansHelper(url, uid);
          }
        });
  },cb_error, cb_ok);
};
seopdb.prototype._killorphansHelper = function(url, uid) {
  var that = this;
  this.is_whitelisted(url, function(wl) {
    if (null==wl) {
      console.log("Deleting:", url, uid);
      that.database.transaction(function(tx) {
          tx.executeSql("DELETE FROM ranks WHERE ?=uid", [ uid ]);
          tx.executeSql("DELETE FROM urls WHERE ?=uid", [ uid ]);
      });
    }
  }
  );
};
return new seopdb;
}();

